import { BaseModel } from './BaseModel'

export interface TagData {
  id?: number
  name: string
  slug: string
  color?: string
  created_at?: string
  updated_at?: string
  post_count?: number
}

export class Tag extends BaseModel {
  protected static tableName = 'tags'

  // 获取所有标签（包含文章数量）
  static async getTagsWithPostCount(): Promise<TagData[]> {
    const sql = `
      SELECT 
        t.*,
        COUNT(pt.project_id) as post_count
      FROM tags t
      LEFT JOIN project_tags pt ON t.id = pt.tag_id
      LEFT JOIN projects p ON pt.project_id = p.id AND p.status = 'published'
      GROUP BY t.id
      ORDER BY post_count DESC, t.name ASC
    `
    const result = await this.query<TagData[]>(sql)
    return result[0] || []
  }

  // 获取热门标签
  static async getPopularTags(limit: number = 10): Promise<TagData[]> {
    const sql = `
      SELECT 
        t.*,
        COUNT(pt.project_id) as post_count
      FROM tags t
      LEFT JOIN project_tags pt ON t.id = pt.tag_id
      LEFT JOIN projects p ON pt.project_id = p.id AND p.status = 'published'
      GROUP BY t.id
      HAVING post_count > 0
      ORDER BY post_count DESC, t.name ASC
      LIMIT ?
    `
    const result = await this.query<TagData[]>(sql, [limit])
    return result[0] || []
  }

  // 根据slug获取标签
  static async getBySlug(slug: string): Promise<TagData | null> {
    const sql = `
      SELECT 
        t.*,
        COUNT(pt.project_id) as post_count
      FROM tags t
      LEFT JOIN project_tags pt ON t.id = pt.tag_id
      LEFT JOIN projects p ON pt.project_id = p.id AND p.status = 'published'
      WHERE t.slug = ?
      GROUP BY t.id
    `
    const result = await this.query<TagData[]>(sql, [slug])
    const tags = result[0] || []
    return tags.length > 0 ? tags[0] ?? null : null
  }

  // 检查slug是否已存在
  static async isSlugExists(slug: string, excludeId?: number): Promise<boolean> {
    let sql = `SELECT COUNT(*) as count FROM tags WHERE slug = ?`
    const params: any[] = [slug]
    
    if (excludeId) {
      sql += ` AND id != ?`
      params.push(excludeId)
    }
    
    const result = await this.query<{count: number}[]>(sql, params)
    return (result[0]?.[0] as {count: number})?.count > 0
  }

  // 创建标签（检查slug唯一性）
  static async createTag(data: Omit<TagData, 'id' | 'created_at' | 'updated_at'>): Promise<number> {
    // 检查slug是否已存在
    if (await this.isSlugExists(data.slug)) {
      throw new Error('标签别名已存在')
    }
    
    try {
      // 直接执行插入操作
      const sql = `
        INSERT INTO ${this.tableName} (name, slug, color)
        VALUES (?, ?, ?)
      `
      const params = [
        data.name,
        data.slug,
        data.color || '#1890ff'
      ]
      
      const result = await this.execute(sql, params)
      return result.insertId
    } catch (error: any) {
      console.error('创建标签失败:', error)
      throw new Error(error.message || '创建标签失败')
    }
  }

  // 更新标签（检查slug唯一性）
  static async updateTag(id: string, data: Partial<TagData>): Promise<boolean> {
    // 如果更新slug，检查是否已存在
    if (data.slug && await this.isSlugExists(data.slug, parseInt(id))) {
      throw new Error('标签别名已存在')
    }
    
    try {
      // 构建更新字段
      const fields: string[] = []
      const params: any[] = []
      
      if (data.name !== undefined) {
        fields.push('name = ?')
        params.push(data.name)
      }
      
      if (data.slug !== undefined) {
        fields.push('slug = ?')
        params.push(data.slug)
      }
      
      if (data.color !== undefined) {
        fields.push('color = ?')
        params.push(data.color)
      }
      
      // 添加更新时间
      fields.push('updated_at = CURRENT_TIMESTAMP')
      
      // 添加ID到参数
      params.push(parseInt(id))
      
      const sql = `
        UPDATE ${this.tableName}
        SET ${fields.join(', ')}
        WHERE id = ?
      `
      
      const result = await this.execute(sql, params)
      return result.affectedRows > 0
    } catch (error: any) {
      console.error('更新标签失败:', error)
      throw new Error(error.message || '更新标签失败')
    }
  }

  // 根据文章ID获取标签
  static async getTagsByPostId(postId: number): Promise<TagData[]> {
    const sql = `
      SELECT t.*
      FROM tags t
      JOIN project_tags pt ON t.id = pt.tag_id
      WHERE pt.project_id = ?
      ORDER BY t.name ASC
    `
    const result = await this.query<TagData[]>(sql, [postId])
    return result[0] || []
  }

  // 为文章添加标签
  static async addTagsToPost(postId: number, tagIds: number[]): Promise<void> {
    // 先删除现有关联
    await this.execute('DELETE FROM project_tags WHERE project_id = ?', [postId])
    
    // 添加新关联
    if (tagIds.length > 0) {
      const values = tagIds.map(tagId => `(${postId}, ${tagId})`).join(', ')
      const sql = `INSERT INTO project_tags (project_id, tag_id) VALUES ${values}`
      await this.execute(sql)
    }
  }

  // 删除标签
  static async delete(id: string | number): Promise<boolean> {
    try {
      const tagId = typeof id === 'string' ? parseInt(id) : id
      
      // 删除与项目的关联关系
      await this.execute('DELETE FROM project_tags WHERE tag_id = ?', [tagId])
      
      // 删除标签
      const sql = `DELETE FROM ${this.tableName} WHERE id = ?`
      const result = await this.execute(sql, [tagId])
      
      return result.affectedRows > 0
    } catch (error: any) {
      console.error('删除标签失败:', error)
      throw new Error(error.message || '删除标签失败')
    }
  }

  // 删除未使用的标签
  static async deleteUnusedTags(): Promise<number> {
    const sql = `
      DELETE FROM tags 
      WHERE id NOT IN (
        SELECT DISTINCT tag_id FROM project_tags
      )
    `
    const result = await this.execute(sql)
    return result.affectedRows
  }
}